﻿CREATE FUNCTION [dbo].[udf_ItemExtract] 
(
      @Position INTEGER,
      @List VARCHAR(MAX),
      @Delimiter VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
      DECLARE @RetVal   VARCHAR(MAX)      SET @RetVal = ''
      DECLARE @Counter  INT               SET @Counter = 0
      DECLARE @StartPos INT               
      DECLARE @Length   INT                     
      DECLARE @DelimPos INT               SET @DelimPos = 0
      
      
      -- SCAN THROUGH UNTIL WE FIND THE ITEM AT THE POSITION OF THE INDEX - WE'LL GO THROUGH WHILE LOOP AT LEAST ONCE
      WHILE @Counter < @Position
      BEGIN
            SET @Counter = @Counter + 1   -- increment counter
            SET @StartPos = @DelimPos + 1 -- move the start position to right after previously found delimiter (or 1st char if this is the fist time through
            SET @DelimPos = CHARINDEX(@Delimiter,@List,@DelimPos + 1)   --find the next delimiter
            -- IF THERE ARE NO DELIMITERS LEFT...
            IF @DelimPos = 0
            BEGIN
                  -- IF WE'RE ON THE POSITION WE'RE LOOKING FOR, THEN SET LENGTH TO THE REST OF THE STRING
                  IF @Counter = @Position SET @Length = (LEN(@List)+1)-@StartPos
                  -- OTHERWISE SET LENGTH TO -1 AND THAT WILL BE AN INDICATOR TO RETURN EMPTY STRING
                  ELSE SET @Length = -1
                  BREAK  
            END
            ELSE SET @Length = @DelimPos - @StartPos  -- otherwise length is now from new start pos right up to delimiter
      END
 
      -- RETURN THE EXTRACTED STRING
      IF @Length > 0 SET @RetVal = SUBSTRING(@List,@StartPos,@Length)
      
      RETURN @RetVal
END